package com.pointi.emoBasedataM;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import com.pointi.emoTools.bdCompareLog;
import com.pointi.emoTools.bdLog;
import com.pointi.emoTools.bdPreProcess;
import com.pointi.emoTools.emoConfig;
import com.pointi.emoTools.emoJDBC;
import com.pointi.emoTools.ezDate;

public class JDBC extends emoJDBC
{
	// ____________________________________________________________________________________________________
	// Default

	public void fnPrepareDatabase()
	{
		String query = "" ;
		query = " EXEC sp_create_sysparam_operate_log ; ";
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_sysparam_df_guideline ; ";
		fnExecuteInsertOrUpdateQuery(query) ;
		query = " EXEC sp_create_sysparam_df_exception ; ";
		fnExecuteInsertOrUpdateQuery(query) ;
		sleep(50) ;
	}

	// ____________________________________________________________________________________________________
	// t_callkey_checklist_yyyymm

	public void fnUpdateCheckList(Date rdate, String inColumn, int inValue, int inCallKey)
	{
		String query = "" ;
		query = " UPDATE t_callkey_checklist_" + ezDate.toString(rdate, ezDate.YYYYMM) + " " + 
				" SET " + inColumn + " = '" + inValue + "' " +
				" WHERE callkey = " + inCallKey ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}
	public void fnUpdateCheckList(Date rdate, String inColumn, int inValue, String inCallKey)
	{
		this.fnUpdateCheckList(rdate, inColumn, inValue, Integer.valueOf(inCallKey)) ;
	}

	// ____________________________________________________________________________________________________
	// t_Ryyyymmdd

	public int fnIsValidCall(String inDate, String inCallkey)
	{
		Statement mST = null ;
		ResultSet mRS = null ;
		String query = "" ;
		query = " SELECT * " +
				" FROM (SELECT COUNT(*) AS cnt " +
				" FROM t_engine_R" + fnGetYYYYMMDD(inDate) + " " +
				" WHERE callkey = " + inCallkey + " " +
				" GROUP BY callkey) AS A " +
				" INNER JOIN " +
				" (SELECT subTIME FROM t_engine_R" + fnGetYYYYMMDD(inDate) +
				" WHERE callkey = " + inCallkey + " and rSTATE = 3) AS B " +
				" ON A.cnt = B.subTIME " ;
		try {
			mST = mConnection.createStatement() ;
			mRS = mST.executeQuery(query) ;
			while (mRS.next()) {
				return mRS.getInt("cnt") ;
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		finally { try { if (mRS != null) mRS.close() ; if (mST != null) mST.close() ; }
		catch (SQLException e) { e.printStackTrace() ; } }
		return 0 ;
	}

	//____________________________________________________________________________________________________
	// DROP // t_engine_Ryyyymmdd

	public void fnDropEngineTable(String inDate)
	{
		String query = "" ;
		query = " EXEC sp_drop_engine_table '" + fnGetYYYYMMDD(inDate) + "' " ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}
	public void fnDeleteRyyyymmdd(String inDate, String inCallkey)
	{
		String query = "" ;
		query = " DELETE FROM t_engine_R" + fnGetYYYYMMDD(inDate) + " " +
				" WHERE callkey = " + inCallkey + "; " ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}

	//____________________________________________________________________________________________________
	// Designed for Emo-Ray v3.0 (2014.06.11)

	//____________________________________________________________________________________________________
	// Emotion Engine Table Update | Y.section > R.side

	public void fnInsertBasedataP(Date rdate, String callkey, emoConfig eCONF) // 2014.06.19
	{
		String mDate = ezDate.toString(rdate, ezDate.YYYYMMDD) ;
		String query = "" ;
		if (n_database_type == DB_TYPE_ORACLE)
			query = "CALL sp_create_t_bd_Pyyyymmdd('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MYSQL)
			query = "CALL sp_create_t_bd_Pyyyymmdd('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MSSQL)
			query = "EXEC sp_create_t_bd_Pyyyymmdd '" + mDate + "';" ;
		fnExecuteInsertOrUpdateQuery(query) ;

		String MIN = String.valueOf(eCONF.AI_MIN()) ;
		String MAX = String.valueOf(eCONF.AI_MAX()) ;

		String R_Table = "t_engine_R" + mDate ;
		String P_Table = "t_bd_P" + mDate ;
		query = " INSERT INTO " + P_Table + "(callkey, subtime, anger, neutral, energy, anger_cnt, neutral_cnt, section, overlap)" +
				" SELECT R.callkey, R.subTIME, " +
				" CASE WHEN (" + MIN + " <= rANGER AND rANGER <= " + MAX + ") THEN R.rANGER ELSE 0 END AS 'rANGER', " +
				" CASE WHEN (" + MIN + " <= rANGER AND rANGER <= " + MAX + ") THEN R.rNEUTRAL ELSE 0 END AS 'rNEUTRAL', " +
				" CASE WHEN (" + MIN + " <= rANGER AND rANGER <= " + MAX + ") THEN R.rENERGY ELSE 0 END AS 'rENERGY', " +
				" CASE WHEN (" + MIN + " <= rANGER AND rANGER <= " + MAX + ") THEN R.rANGERCNT ELSE 0 END AS 'rANGERCNT', " +
				" CASE WHEN (" + MIN + " <= rANGER AND rANGER <= " + MAX + ") THEN R.rNEUTRALCNT ELSE 0 END AS 'rNEUTRALCNT', " +
				" CASE WHEN (" + MIN + " <= rANGER AND rANGER <= " + MAX + ") THEN R.rSIDE ELSE 0 END AS 'nSIDE', 0" +
				" FROM " + R_Table + " AS R " +
				" WHERE R.callkey = " + callkey + 
				" ORDER BY R.callkey, R.subTIME ;" ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}

	//____________________________________________________________________________________________________
	// t_bd_Pyyyymmdd

	public void fnGetBasedataP(Date rdate, ArrayList<bdPreProcess> P, String callkey) // 2014.06.19
	{
		String mDate = ezDate.toString(rdate, ezDate.YYYYMMDD) ;
		String query = "" ;

		String P_Table = "t_bd_P" + mDate ;
		query = " SELECT * " +
				" FROM " + P_Table + " AS P " +
				" WHERE P.callkey = " + callkey + 
				" ORDER BY P.subtime ;" ;

		int subtime = 0 ;
		double anger = 0 ;
		double neutral = 0 ;
		double energy = 0 ;
		int anger_cnt = 0 ;
		int neutral_cnt = 0 ;
		int section = 0 ;
		int overlap = 0 ;

		try
		{
			Statement mStatement = mConnection.createStatement() ;
			ResultSet mRS = mStatement.executeQuery(query) ;
			while(mRS.next())
			{
				subtime = mRS.getInt("subtime") ;
				anger = mRS.getDouble("anger") ;
				neutral = mRS.getDouble("neutral") ;
				energy = mRS.getDouble("energy") ;
				anger_cnt = mRS.getInt("anger_cnt") ;
				neutral_cnt = mRS.getInt("neutral_cnt") ;
				section = mRS.getInt("section") ;
				overlap = mRS.getInt("overlap") ;
				P.add(new bdPreProcess(Integer.valueOf(callkey), subtime, anger, neutral, energy, anger_cnt, neutral_cnt, section, overlap)) ;
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
	}

	//____________________________________________________________________________________________________
	// t_bd_Pyyyymmdd

	public void fnGetBasedataR(Date rdate, bdLog inData, int start_time) // 2014.06.19
	{
		String mDate = ezDate.toString(rdate, ezDate.YYYYMMDD) ;
		String query = "" ;

		String P_Table = "t_bd_P" + mDate ;
		if (inData.section() == bdLog.LOG_FULL)
		{
			query = " SELECT R.callkey, ((R.A*100.0)/R.eCNT) AS AI, ((R.N*100.0)/R.eCNT) AS NI, R.E/R.CNT AS EI, R.CNT AS TT" +
					" FROM (SELECT P.callkey, SUM(P.anger_cnt) AS A, SUM(P.neutral_cnt) AS N, SUM(P.energy) AS E, (SUM(P.anger_cnt) + SUM(P.neutral_cnt)) AS eCNT, COUNT(*) AS CNT" +
					" FROM " + P_Table + " AS P" +
					" WHERE P.callkey = " + String.valueOf(inData.callkey()) + " AND P.anger > 0 AND P.section > 0 " + " AND P.subtime >= " + start_time +  
					" GROUP BY P.callkey) AS R ;" ;
		}
		else
		{
			query = " SELECT R.callkey, ((R.A*100.0)/R.eCNT) AS AI, ((R.N*100.0)/R.eCNT) AS NI, R.E/R.CNT AS EI, R.CNT AS TT" +
					" FROM (SELECT P.callkey, SUM(P.anger_cnt) AS A, SUM(P.neutral_cnt) AS N, SUM(P.energy) AS E, (SUM(P.anger_cnt) + SUM(P.neutral_cnt)) AS eCNT, COUNT(*) AS CNT" +
					" FROM " + P_Table + " AS P" +
					" WHERE P.callkey = " + String.valueOf(inData.callkey()) + " AND P.anger > 0 AND P.subtime >= " + start_time + " AND P.section = " + inData.section() +
					" GROUP BY P.callkey) AS R ;" ;
		}

		try
		{
			Statement mStatement = mConnection.createStatement() ;
			ResultSet mRS = mStatement.executeQuery(query) ;
			while(mRS.next())
			{
				inData.ai(mRS.getDouble("AI")) ;
				inData.ni(mRS.getDouble("NI")) ;
				inData.ei(mRS.getDouble("EI")) ;
				inData.tc(mRS.getInt("TT")) ;
				inData.tt(mRS.getInt("TT")) ;
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }		
	}

	//____________________________________________________________________________________________________
	// t_bd_agent_log_yyyymm

	public void fnInsertBasedataAgent(Date rdate, String log_query) // 2014.06.19
	{
		String mDate = ezDate.toString(rdate, ezDate.YYYYMM) ;
		String query = "" ;
		if (n_database_type == DB_TYPE_ORACLE)
			query = "CALL sp_create_t_bd_agent_log('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MYSQL)
			query = "CALL sp_create_t_bd_agent_log('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MSSQL)
			query = "EXEC sp_create_t_bd_agent_log '" + mDate + "';" ;
		fnExecuteInsertOrUpdateQuery(query) ;
		fnExecuteInsertOrUpdateQuery(log_query) ;
	}

	//____________________________________________________________________________________________________
	// t_bd_customer_log_yyyymm

	public void fnInsertBasedataCustomer(Date rdate, String log_query) // 2014.06.19
	{
		String mDate = ezDate.toString(rdate, ezDate.YYYYMM) ;
		String query = "" ;
		if (n_database_type == DB_TYPE_ORACLE)
			query = "CALL sp_create_t_bd_customer_log('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MYSQL)
			query = "CALL sp_create_t_bd_customer_log('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MSSQL)
			query = "EXEC sp_create_t_bd_customer_log '" + mDate + "';" ;
		fnExecuteInsertOrUpdateQuery(query) ;
		fnExecuteInsertOrUpdateQuery(log_query) ;
	}

	//____________________________________________________________________________________________________
	// t_bd_full_log_yyyymm

	public void fnInsertBasedataFull(Date rdate, String log_query) // 2014.06.19
	{
		String mDate = ezDate.toString(rdate, ezDate.YYYYMM) ;
		String query = "" ;
		if (n_database_type == DB_TYPE_ORACLE)
			query = "CALL sp_create_t_bd_full_log('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MYSQL)
			query = "CALL sp_create_t_bd_full_log('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MSSQL)
			query = "EXEC sp_create_t_bd_full_log '" + mDate + "';" ;
		fnExecuteInsertOrUpdateQuery(query) ;
		fnExecuteInsertOrUpdateQuery(log_query) ;
	}

	//____________________________________________________________________________________________________
	// Overlap & Ready List

	public void fnUpdateCrossPoint(Date rdate, String callkey) // 2014.06.19
	{
		String mDate = ezDate.toString(rdate, ezDate.YYYYMMDD) ;
		String query = "" ;
		String P_Table = "t_bd_P" + mDate ;
		query = " UPDATE " + P_Table + 
				" SET " + P_Table + ".overlap = COMP.DIR " +
				" FROM (SELECT P.callkey, (P.subtime + P.CNT - 1) AS 'subtime', P.COMP AS 'DIR' " +
				" FROM (SELECT P1.callkey AS 'callkey', P1.subtime AS 'subtime', P1.[section] AS 'SEC1', P2.[section] AS 'SEC2', P1.CNT AS 'CNT', P1.SEQ AS 'S1', P2.SEQ AS 'S2', " +
				" CASE WHEN P1.[section] = 1 AND P2.[section] = 2 THEN 12 WHEN P1.[section] = 2 AND P2.[section] = 1 THEN 21 ELSE -1 END AS 'COMP' " +
				" FROM (SELECT MIN(P.callkey) AS 'callkey', MIN(P.subtime) AS 'subtime', MIN(P.[section]) AS 'section', COUNT(*) AS 'CNT', RANK() OVER (ORDER BY MIN(P.subtime)) AS 'SEQ' " +
				" FROM (SELECT G.callkey AS 'callkey', G.subtime AS 'subtime', G.[section] AS 'section', (G.subtime - ROW_NUMBER() OVER(PARTITION BY G.[section] ORDER BY G.subtime)) AS 'RN' " +
				" FROM (SELECT P.callkey, P.subtime, P.[section] FROM " + P_Table + " AS P WHERE P.callkey = " + callkey + ") AS G) AS P GROUP BY P.RN, P.[section]) P1 INNER JOIN " +
				" (SELECT MIN(P.callkey) AS 'callkey', MIN(P.subtime) AS 'subtime', MIN(P.[section]) AS 'section', COUNT(*) AS 'CNT', (RANK() OVER (ORDER BY MIN(P.subtime)) - 1) AS 'SEQ' " +
				" FROM (SELECT G.callkey AS 'callkey', G.subtime AS 'subtime', G.[section] AS 'section', (G.subtime - ROW_NUMBER() OVER(PARTITION BY G.[section] ORDER BY G.subtime)) AS 'RN' " +
				" FROM (SELECT P.callkey, P.subtime, P.[section] FROM " + P_Table + " AS P WHERE P.callkey = " + callkey + ") AS G) AS P GROUP BY P.RN, P.[section]) P2 ON P1.callkey = P2.callkey AND P1.SEQ = P2.SEQ ) AS P " +
				" WHERE P.COMP != -1) AS COMP " +
				" WHERE " + P_Table + ".callkey = COMP.callkey AND " + P_Table + ".subtime = COMP.subtime ; " ;
		fnExecuteInsertOrUpdateQuery(query) ;
	}

	//____________________________________________________________________________________________________
	// Overlap & Ready List

	public void fnCompareSection(Date rdate, String callkey, bdCompareLog COMP) // 2014.06.19
	{
		String mDate = ezDate.toString(rdate, ezDate.YYYYMMDD) ;
		String query = "" ;
		String P_Table = "t_bd_P" + mDate ;
		query = " SELECT P.callkey, P.CNT AS 'CNT', P.COMP AS 'DIR' " +
				" FROM (SELECT P1.callkey AS 'callkey', P1.subtime AS 'subtime', P1.[section] AS 'SEC1', P2.[section] AS 'SEC2', P1.CNT AS 'CNT', P1.SEQ AS 'S1', P2.SEQ AS 'S2', " +
				" CASE WHEN P1.[section] = 1 AND P2.[section] = 2 THEN 12 WHEN P1.[section] = 2 AND P2.[section] = 1 THEN 21 ELSE -1 END AS 'COMP' " +
				" FROM (SELECT MIN(P.callkey) AS 'callkey', MIN(P.subtime) AS 'subtime', MIN(P.[section]) AS 'section', COUNT(*) AS 'CNT', RANK() OVER (ORDER BY MIN(P.subtime)) AS 'SEQ' " +
				" FROM (SELECT G.callkey AS 'callkey', G.subtime AS 'subtime', G.[section] AS 'section', (G.subtime - ROW_NUMBER() OVER(PARTITION BY G.[section] ORDER BY G.subtime)) AS 'RN' " +
				" FROM (SELECT P.callkey, P.subtime, P.[section] FROM " + P_Table + " AS P WHERE P.callkey = " + callkey + ") AS G) AS P GROUP BY P.RN, P.[section]) P1 INNER JOIN " +
				" (SELECT MIN(P.callkey) AS 'callkey', MIN(P.subtime) AS 'subtime', MIN(P.[section]) AS 'section', COUNT(*) AS 'CNT', (RANK() OVER (ORDER BY MIN(P.subtime)) - 1) AS 'SEQ' " +
				" FROM (SELECT G.callkey AS 'callkey', G.subtime AS 'subtime', G.[section] AS 'section', (G.subtime - ROW_NUMBER() OVER(PARTITION BY G.[section] ORDER BY G.subtime)) AS 'RN' " +
				" FROM (SELECT P.callkey, P.subtime, P.[section] FROM " + P_Table + " AS P WHERE P.callkey = " + callkey + ") AS G) AS P GROUP BY P.RN, P.[section]) P2 ON P1.callkey = P2.callkey AND P1.SEQ = P2.SEQ ) AS P " +
				" WHERE P.COMP != -1 ; " ;
		try {
			Statement mStatement = mConnection.createStatement() ;
			ResultSet mRS = mStatement.executeQuery(query) ;
			while(mRS.next()) {
				if (mRS.getInt("DIR") == 12) {
					COMP.add_vavb_rc() ;
					COMP.add_vavb_rt(mRS.getInt("CNT")) ;
				} else {
					COMP.add_vbva_rc() ;
					COMP.add_vbva_rt(mRS.getInt("CNT")) ;
				}
			}
		} catch(SQLException se) { System.out.println(se.getMessage()) ; }		
	}

	//____________________________________________________________________________________________________
	// t_bd_compare_log_yyyymm

	public void fnInsertBasedataCompare(Date rdate, bdCompareLog COMP) // 2014.07.04
	{
		System.out.println("[BD] INSERT COMP-TABLE") ;
		String mDate = ezDate.toString(rdate, ezDate.YYYYMM) ;
		String query = "" ;
		if (n_database_type == DB_TYPE_ORACLE)
			query = "CALL sp_create_t_bd_compare_log('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MYSQL)
			query = "CALL sp_create_t_bd_compare_log('" + mDate + "');" ;
		else if (n_database_type == DB_TYPE_MSSQL)
			query = "EXEC sp_create_t_bd_compare_log '" + mDate + "';" ;
		fnExecuteInsertOrUpdateQuery(query) ;
		fnExecuteInsertOrUpdateQuery(COMP.fnGetQuery(mDate)) ;		
	}

	//____________________________________________________________________________________________________
	// Basedata Scheduler

	public Date fnCheckUnhandledList(Date date) // 2014.07.04
	{
		String yyyymm = ezDate.toString(date, ezDate.YYYYMM) ;
		String tableA = "t_callkey_" + yyyymm ;
		String tableB = "t_callkey_checklist_" + yyyymm ;
		String query = "" ;
		query = "SELECT TOP 1 A.rdate, COUNT(*) AS 'cnt' " +
				"FROM " + tableA + " AS A INNER JOIN " + tableB + " AS B ON A.seq = B.callkey " +
				"WHERE B.check_client = 3 AND B.check_engine = 3 AND B.used = 0 " +
				"GROUP BY A.rdate ORDER BY A.rdate ASC ;" ;

		boolean isNull = true ;
		int cnt = 0 ;
		Date rdate = new Date() ;
		try {
			Statement mStatement = mConnection.createStatement() ;
			ResultSet mRS = mStatement.executeQuery(query) ;
			while(mRS.next()) {
				isNull = false ;
				cnt = mRS.getInt("cnt") ;
				rdate = ezDate.fnStringToDate(mRS.getString("rdate")) ;
				this.out("[Unhandled List][Today] Date [" + ezDate.toString(rdate, ezDate.YYYYMMDD_BAR) + "] Count [" + cnt + "]") ;
			}
			if (isNull) {
				this.out("[Unhandled List][None] Date [" + ezDate.toString(rdate, ezDate.YYYYMMDD_BAR) + "] Count [" + cnt + "]") ;
				rdate = null ;
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
		return rdate ;
	}

	//____________________________________________________________________________________________________
	// Basedata Scheduler

	public void fnGetUnhandledList(Date date, ArrayList<String> targetList)
	{
		String yyyymm = ezDate.toString(date, ezDate.YYYYMM) ;
		String tableA = "t_callkey_" + yyyymm ;
		String tableB = "t_callkey_checklist_" + yyyymm ;
		String query = "" ;
		query = "SELECT B.callkey " +
				"FROM " + tableA + " AS A INNER JOIN " + tableB + " AS B ON A.seq = B.callkey " +
				"WHERE B.check_client = 3 AND B.check_engine = 3 AND B.used = 0 AND A.rdate = '" + ezDate.toString(date, ezDate.YYYYMMDD_BAR) + "' " + 
				"ORDER BY A.rdate ASC ;" ;
		try
		{
			Statement mStatement = mConnection.createStatement() ;
			ResultSet mRS = mStatement.executeQuery(query) ;
			while(mRS.next())
			{
				int callkey = mRS.getInt("callkey") ;
				if(!targetList.contains(String.valueOf(callkey))) {
					targetList.add(String.valueOf(callkey)) ;
				}
			}
		}
		catch(SQLException se) { System.out.println(se.getMessage()) ; }
	}

	//____________________________________________________________________________________________________

	public void out(Object obj)
	{
		System.out.println(obj) ;
	}

	//____________________________________________________________________________________________________
	// EOC
}
